#!/bin/bash

#
# PostgreSQL log parser
# =====================
# pglogparser is a tool to extract the DML statements (INSERT, UPDATE, DELETE) 
# from PostgreSQL's log when log_statement option is enabled.
# 
# Henry Molina
# 

file=$1

gawk -F: '
function prompt(label,line,text) 
{
    gsub(/^[ \t]*/,"",text)
    OFS="|"
    if (line == "") {
        for(j=1;j<=w;j++) { line = line" " }
    } else {
        w=length(line)
    }
    print label,line,text
}
{
    IGNORECASE = 1
    s="parameters: "
    c = 1
    # set the extraction column number
    for(i=1;i<=NF;i++) {
        if ($i ~ /execute/) {
            column = i
            break
        }      
    }
    if ($column ~ /execute/ && $(column+1) ~ /update|insert|delete/ ){
        sql = $(column+1)
        while(getline nl > 0) {
            if (nl ~ s){ 
                # Is a "parameters" line!

                prompt("statement ",NR-c,sql)
                # Change "$" for "°" to search
                gsub(/\$/,"°",sql)

                # From the new line extracts the parameters
                parameters = substr(nl,index(nl,s)+length(s))
                prompt("parameters",NR,parameters)
                n=split(parameters,list,",")
                for(i=1; i<n; i++){
                    # set <<$key = value>>
                    split(list[i],kv," = ")
                    # change <<$key = value>>
                    gsub("°"i",",kv[2]",",sql)
                }
                split(list[n],kv," = ")
                gsub("°"n")",kv[2]")",sql)
                gsub("°","$",sql)
                c = 1
                break
            } else {
                gsub(/^[ \t]*/,"",nl)
                sql = sql" "nl
                c++
            }
        }
        prompt("parsing   ","",sql";")
    } 
}
' $file

